import traceback
import pyautogui
import os
import pyperclip as clip
import json
import pandas as pd
from sqlalchemy import create_engine
import pypinyin
from bs4 import BeautifulSoup
import random
from jsjj.util.configUtil import *
from jsjj.util.dirUtil import *
from jsjj.util.timeUtil import *
from jsjj.util.dbUtil import *
from jsjj.util.emailUtil import *
from jsjj.util.logUtil import *
from jsjj.util.mouseMoveUtil import *

# 所有京东的，都没有做断电续传的
# 运行模式: 日模式；周模式；月模式
runMode = '月模式'
modeName = '京东_POP_行业大盘_商品榜单_交易榜单TOP100'
# 京东纯自营的大盘数据-行业-商品榜单-交易榜单(交易榜单TOP100)
# 原来是下载文件，现在修改后的不是下载文件了
# downloadFilePath = 'D:/360安全浏览器下载'
deta = 35
waitSecond = 3
# 默认抓取上个月的数据
month = str(datetime.datetime.now().month - 1) + "月"
newfix_x = 213
downloadMonth = {'1月': [1215, 341], '2月': [1290, 341], '3月': [1368, 341],
                 '4月': [1215, 392], '5月': [1290, 392], '6月': [1368, 392],
                 '7月': [1215, 444], '8月': [1290, 444], '9月': [1368, 444],
                 '10月': [1215, 496], '11月': [1290, 496], '12月': [1368, 496]}

downloadWeek = {'1': [1232, 359], '2': [1232, 389], '3': [1232, 419],
                '4': [1232, 449], '5': [1232, 479]}

def fixFormatImageColumn(imageSrc):
    return str(imageSrc).replace("//", "")


def TopAndLeftMenu():
    pyautogui.FAILSAFE = False

    sleep(6)

    closePOPTOPFIX()

    pyautogui.keyDown('f5')
    pyautogui.keyUp('f5')

    # 顶部行业
    pyautogui.moveTo(1086, 191, 2)
    pyautogui.click()

    sleep(6)

    # 左边商品榜单
    pyautogui.moveTo(436, 416, 2)
    pyautogui.click()

    # 还需要组合其他数据，因此需要F12
    sleep(4)
    pyautogui.keyDown('f12')
    pyautogui.keyUp('f12')
    sleep(4)

    # 防止bug
    pyautogui.moveTo(512, 258, 2)
    pyautogui.click()

    if runMode == '月模式':
        # 顶层按月查询
        pyautogui.moveTo(1110, 256, 2)
        # 1097
        pyautogui.moveTo(1110, 372, 2)

        pyautogui.moveTo(1213, 372, 2)

        # 选择到月份(6月)
        pyautogui.moveTo(downloadMonth[month][0], downloadMonth[month][1], 2)
        pyautogui.click()
    elif runMode == '周模式':
        # 顶层按周查询
        pyautogui.moveTo(1099, 257, 2)
        pyautogui.moveTo(1099, 335, 2)

        pyautogui.moveTo(1228, 335, 2)

        # 选择到周
        currentWeekInThisMonth = getWeekOfMonth(datetime.datetime.now().year, datetime.datetime.now().month,
                                                datetime.datetime.now().day)

        # fix bug
        pyautogui.moveTo(1414, 335, 2)
        # 动态计算出来，默认上周的数据
        currentWeekInThisMonth = currentWeekInThisMonth - 1
        pyautogui.moveTo(downloadWeek[str(currentWeekInThisMonth)][0], downloadWeek[str(currentWeekInThisMonth)][1], 4)
        pyautogui.click()

    # 关闭月份菜单
    pyautogui.keyDown('esc')
    pyautogui.keyUp('esc')


def LoopMenu(conn):
    # ★其他服务(1)
    for i in range(0, 1):
        # ========顶层菜单【客厅家具】=======
        # clear network
        pyautogui.moveTo(1541, 144, 4)
        pyautogui.doubleClick()

        # filter textbox
        pyautogui.moveTo(1515, 171, 1)
        pyautogui.doubleClick()

        clip.copy('getProductBillBoardDealData')  # 先复制
        pyautogui.hotkey('ctrl', 'v')  # 再粘贴

        # old code
        sleep(waitSecond)
        pyautogui.moveTo(1015 - newfix_x, 259)
        pyautogui.click()

        # 一级
        pyautogui.moveTo(997 - newfix_x, 330 + deta * -1, 2)
        # 二级
        pyautogui.moveTo(1192 - newfix_x, 330 + deta * -1, 2)
        # 二级（变化）
        pyautogui.moveTo(1192 - newfix_x, 298 + i * deta, 2)
        # 最后才能点击链接（二级菜单）
        pyautogui.click()

        # ===-由于需要关联，因此在这里保存信息
        # 选中第一个文件
        pyautogui.moveTo(1541, 326, 2)
        pyautogui.click()

        # 选中json区域
        pyautogui.moveTo(1830, 492, 2)
        pyautogui.click()

        sleep(2)
        pyautogui.hotkey('ctrl', 'a')

        sleep(2)
        pyautogui.hotkey('ctrl', 'c')

        # 读取剪切板内容json内容
        sleep(2)
        value = clip.paste()
        jsonFromWeb = json.loads(value)
        df = pd.read_json(json.dumps(jsonFromWeb['content']['trade']['data']))
        df.rename(
            columns={0: '排名', 1: '商品名称', 2: '成交金额指数', 3: '成交单量指数', 4: '访客指数', 5: '搜索点击指数', 6: '关注人数',
                     8: '平台商品SKUID', }, inplace=True)
        # 4个图片链接的信息
        for j in range(0, 4):
            # filter textbox
            pyautogui.moveTo(1515, 171, 1)
            pyautogui.doubleClick()

            clip.copy('getImageURL')  # 先复制
            pyautogui.hotkey('ctrl', 'v')  # 再粘贴

            # 选中第一个文件
            pyautogui.moveTo(1541, 326 + j * 20, 2)
            pyautogui.click()

            # 选中json区域
            pyautogui.moveTo(1830, 492, 2)
            pyautogui.click()

            sleep(2)
            pyautogui.hotkey('ctrl', 'a')

            sleep(2)
            pyautogui.hotkey('ctrl', 'c')

            # 读取剪切板内容json内容
            sleep(2)
            value = clip.paste()
            jsonImage = json.loads(value)
            dfImageLoop = pd.read_json(json.dumps(jsonImage['content']['data']))
            if j == 0:
                dfImage = dfImageLoop
            else:
                dfImage = pd.concat([dfImage, dfImageLoop])

        dfImage['图片链接'] = dfImage['imgSrc'].apply(fixFormatImageColumn)

        dfAll = pd.merge(df, dfImage, left_on=['平台商品SKUID'], right_on=['skuId'], how='left')
        dfAll = dfAll.drop(columns=['skuId', 'currentPrice', 'imgSrc', 7])
        dfAll = dfAll.drop_duplicates()
        # =====还需要获取图片链接信息

        # 不需要通过页面的文件下载，直接保存到数据库中了
        if i == 0:
            saveDFToMysql(dfAll, '', '其他服务', '有价优惠券', '', conn)

        sleep(3)

    # ★卧室家具(9->11)
    for i in range(0, 11):
        # ========顶层菜单【客厅家具】=======
        # clear network
        pyautogui.moveTo(1541, 144, 4)
        pyautogui.doubleClick()

        # filter textbox
        pyautogui.moveTo(1515, 171, 1)
        pyautogui.doubleClick()

        clip.copy('getProductBillBoardDealData')  # 先复制
        pyautogui.hotkey('ctrl', 'v')  # 再粘贴

        # old code
        pyautogui.moveTo(1015 - newfix_x, 259)
        pyautogui.click()
        # 一级
        pyautogui.moveTo(997 - newfix_x, 330 + deta * 0, 2)
        # 二级
        pyautogui.moveTo(1192 - newfix_x, 330 + deta * 0, 2)
        # 二级（变化）
        if i <= 8:
            pyautogui.moveTo(1192 - newfix_x, 298 + i * deta, 2)
        elif i == 9:
            pyautogui.scroll(-60)
            pyautogui.moveTo(1192 - newfix_x, 298 + 7 * deta + deta - 15, 2)
        elif i == 10:
            pyautogui.scroll(-60)
            pyautogui.moveTo(1192 - newfix_x, 298 + 8 * deta + deta - 15, 2)

        pyautogui.click()

        # ===-由于需要关联，因此在这里保存信息
        # 选中第一个文件
        pyautogui.moveTo(1541, 326, 2)
        pyautogui.click()

        # 选中json区域
        pyautogui.moveTo(1830, 492, 2)
        pyautogui.click()

        sleep(2)
        pyautogui.hotkey('ctrl', 'a')

        sleep(2)
        pyautogui.hotkey('ctrl', 'c')

        # 读取剪切板内容json内容
        sleep(2)
        value = clip.paste()
        jsonFromWeb = json.loads(value)
        df = pd.read_json(json.dumps(jsonFromWeb['content']['trade']['data']))
        df.rename(
            columns={0: '排名', 1: '商品名称', 2: '成交金额指数', 3: '成交单量指数', 4: '访客指数', 5: '搜索点击指数', 6: '关注人数',
                     8: '平台商品SKUID', }, inplace=True)
        # 4个图片链接的信息
        for j in range(0, 4):
            # filter textbox
            pyautogui.moveTo(1515, 171, 1)
            pyautogui.doubleClick()

            clip.copy('getImageURL')  # 先复制
            pyautogui.hotkey('ctrl', 'v')  # 再粘贴

            # 选中第一个文件
            pyautogui.moveTo(1541, 326 + j * 20, 2)
            pyautogui.click()

            # 选中json区域
            pyautogui.moveTo(1830, 492, 2)
            pyautogui.click()

            sleep(2)
            pyautogui.hotkey('ctrl', 'a')

            sleep(2)
            pyautogui.hotkey('ctrl', 'c')

            # 读取剪切板内容json内容
            sleep(2)
            value = clip.paste()
            jsonImage = json.loads(value)
            dfImageLoop = pd.read_json(json.dumps(jsonImage['content']['data']))
            if j == 0:
                dfImage = dfImageLoop
            else:
                dfImage = pd.concat([dfImage, dfImageLoop])

        sleep(3)

        dfImage['图片链接'] = dfImage['imgSrc'].apply(fixFormatImageColumn)

        dfAll = pd.merge(df, dfImage, left_on=['平台商品SKUID'], right_on=['skuId'], how='left')
        dfAll = dfAll.drop(columns=['skuId', 'currentPrice', 'imgSrc', 7])
        dfAll = dfAll.drop_duplicates()
        # =====还需要获取图片链接信息

        # 不需要通过页面的文件下载，直接保存到数据库中了
        if i == 0:
            saveDFToMysql(dfAll, '', '卧室家具', '床', '', conn)
        elif i == 1:
            saveDFToMysql(dfAll, '', '卧室家具', '床垫', '', conn)
        elif i == 2:
            saveDFToMysql(dfAll, '', '卧室家具', '床头柜', '', conn)
        elif i == 3:
            saveDFToMysql(dfAll, '', '卧室家具', '简易衣柜', '', conn)
        elif i == 4:
            saveDFToMysql(dfAll, '', '卧室家具', '斗柜', '', conn)
        elif i == 5:
            saveDFToMysql(dfAll, '', '卧室家具', '梳妆台/凳子', '', conn)
        elif i == 6:
            saveDFToMysql(dfAll, '', '卧室家具', '穿衣镜', '', conn)
        elif i == 7:
            saveDFToMysql(dfAll, '', '卧室家具', '衣柜', '', conn)
        elif i == 8:
            saveDFToMysql(dfAll, '', '卧室家具', '特殊商品', '', conn)
        elif i == 9:
            saveDFToMysql(dfAll, '', '卧室家具', '定制衣柜', '', conn)
        elif i == 10:
            saveDFToMysql(dfAll, '', '卧室家具', '榻榻米', '', conn)

    # ★客厅家具(9)
    for i in range(0, 9):
        # ========顶层菜单【客厅家具】=======
        # clear network
        pyautogui.moveTo(1541, 144, 4)
        pyautogui.doubleClick()

        # filter textbox
        pyautogui.moveTo(1515, 171, 1)
        pyautogui.doubleClick()

        clip.copy('getProductBillBoardDealData')  # 先复制
        pyautogui.hotkey('ctrl', 'v')  # 再粘贴

        # old code
        sleep(waitSecond)
        pyautogui.moveTo(1015 - newfix_x, 259)
        pyautogui.click()
        # 一级
        pyautogui.moveTo(997 - newfix_x, 330 + deta * 1, 2)
        # 二级
        pyautogui.moveTo(1192 - newfix_x, 330 + deta * 1, 2)
        # 二级（变化）
        pyautogui.moveTo(1192 - newfix_x, 298 + i * deta, 2)
        pyautogui.click()

        # ===-由于需要关联，因此在这里保存信息
        # 选中第一个文件
        pyautogui.moveTo(1541, 326, 2)
        pyautogui.click()

        # 选中json区域
        pyautogui.moveTo(1830, 492, 2)
        pyautogui.click()

        sleep(2)
        pyautogui.hotkey('ctrl', 'a')

        sleep(2)
        pyautogui.hotkey('ctrl', 'c')

        # 读取剪切板内容json内容
        sleep(2)
        value = clip.paste()
        jsonFromWeb = json.loads(value)
        df = pd.read_json(json.dumps(jsonFromWeb['content']['trade']['data']))
        df.rename(
            columns={0: '排名', 1: '商品名称', 2: '成交金额指数', 3: '成交单量指数', 4: '访客指数', 5: '搜索点击指数', 6: '关注人数',
                     8: '平台商品SKUID', }, inplace=True)
        # 4个图片链接的信息
        for j in range(0, 4):
            # filter textbox
            pyautogui.moveTo(1515, 171, 1)
            pyautogui.doubleClick()

            clip.copy('getImageURL')  # 先复制
            pyautogui.hotkey('ctrl', 'v')  # 再粘贴

            # 选中第一个文件
            pyautogui.moveTo(1541, 326 + j * 20, 2)
            pyautogui.click()

            # 选中json区域
            pyautogui.moveTo(1830, 492, 2)
            pyautogui.click()

            sleep(2)
            pyautogui.hotkey('ctrl', 'a')

            sleep(2)
            pyautogui.hotkey('ctrl', 'c')

            # 读取剪切板内容json内容
            sleep(2)
            value = clip.paste()
            jsonImage = json.loads(value)
            dfImageLoop = pd.read_json(json.dumps(jsonImage['content']['data']))
            if j == 0:
                dfImage = dfImageLoop
            else:
                dfImage = pd.concat([dfImage, dfImageLoop])

        sleep(3)

        dfImage['图片链接'] = dfImage['imgSrc'].apply(fixFormatImageColumn)

        dfAll = pd.merge(df, dfImage, left_on=['平台商品SKUID'], right_on=['skuId'], how='left')
        dfAll = dfAll.drop(columns=['skuId', 'currentPrice', 'imgSrc', 7])
        dfAll = dfAll.drop_duplicates()
        # =====还需要获取图片链接信息

        # 不需要通过页面的文件下载，直接保存到数据库中了
        if i == 0:
            saveDFToMysql(dfAll, '', '客厅家具', '沙发', '', conn)
        elif i == 1:
            saveDFToMysql(dfAll, '', '客厅家具', '沙发床', '', conn)
        elif i == 2:
            saveDFToMysql(dfAll, '', '客厅家具', '边桌/茶几', '', conn)
        elif i == 3:
            saveDFToMysql(dfAll, '', '客厅家具', '电视柜', '', conn)
        elif i == 4:
            saveDFToMysql(dfAll, '', '客厅家具', '屏风', '', conn)
        elif i == 5:
            saveDFToMysql(dfAll, '', '客厅家具', '壁炉', '', conn)
        elif i == 6:
            saveDFToMysql(dfAll, '', '客厅家具', '鞋柜', '', conn)
        elif i == 7:
            saveDFToMysql(dfAll, '', '客厅家具', '凳子', '', conn)
        elif i == 8:
            saveDFToMysql(dfAll, '', '客厅家具', '特殊商品', '', conn)

    # ★餐厅家具(5)
    for i in range(0, 5):
        # ========顶层菜单【客厅家具】=======
        # clear network
        pyautogui.moveTo(1541, 144, 4)
        pyautogui.doubleClick()

        # filter textbox
        pyautogui.moveTo(1515, 171, 1)
        pyautogui.doubleClick()

        clip.copy('getProductBillBoardDealData')  # 先复制
        pyautogui.hotkey('ctrl', 'v')  # 再粘贴

        # old code
        sleep(waitSecond)
        pyautogui.moveTo(1015 - newfix_x, 259)
        pyautogui.click()
        # 一级
        pyautogui.moveTo(997 - newfix_x, 330 + deta * 2, 2)
        # 二级
        pyautogui.moveTo(1192 - newfix_x, 330 + deta * 2, 2)
        # 二级（变化）
        pyautogui.moveTo(1192 - newfix_x, 298 + i * deta, 2)
        pyautogui.click()

        # ===-由于需要关联，因此在这里保存信息
        # 选中第一个文件
        pyautogui.moveTo(1541, 326, 2)
        pyautogui.click()

        # 选中json区域
        pyautogui.moveTo(1830, 492, 2)
        pyautogui.click()

        sleep(2)
        pyautogui.hotkey('ctrl', 'a')

        sleep(2)
        pyautogui.hotkey('ctrl', 'c')

        # 读取剪切板内容json内容
        sleep(2)
        value = clip.paste()
        jsonFromWeb = json.loads(value)
        df = pd.read_json(json.dumps(jsonFromWeb['content']['trade']['data']))
        df.rename(
            columns={0: '排名', 1: '商品名称', 2: '成交金额指数', 3: '成交单量指数', 4: '访客指数', 5: '搜索点击指数', 6: '关注人数',
                     8: '平台商品SKUID', }, inplace=True)
        # 4个图片链接的信息
        for j in range(0, 4):
            # filter textbox
            pyautogui.moveTo(1515, 171, 1)
            pyautogui.doubleClick()

            clip.copy('getImageURL')  # 先复制
            pyautogui.hotkey('ctrl', 'v')  # 再粘贴

            # 选中第一个文件
            pyautogui.moveTo(1541, 326 + j * 20, 2)
            pyautogui.click()

            # 选中json区域
            pyautogui.moveTo(1830, 492, 2)
            pyautogui.click()

            sleep(2)
            pyautogui.hotkey('ctrl', 'a')

            sleep(2)
            pyautogui.hotkey('ctrl', 'c')

            # 读取剪切板内容json内容
            sleep(2)
            value = clip.paste()
            jsonImage = json.loads(value)
            dfImageLoop = pd.read_json(json.dumps(jsonImage['content']['data']))
            if j == 0:
                dfImage = dfImageLoop
            else:
                dfImage = pd.concat([dfImage, dfImageLoop])

        sleep(3)

        dfImage['图片链接'] = dfImage['imgSrc'].apply(fixFormatImageColumn)

        dfAll = pd.merge(df, dfImage, left_on=['平台商品SKUID'], right_on=['skuId'], how='left')
        dfAll = dfAll.drop(columns=['skuId', 'currentPrice', 'imgSrc', 7])
        dfAll = dfAll.drop_duplicates()
        # =====还需要获取图片链接信息

        # 不需要通过页面的文件下载，直接保存到数据库中了
        if i == 0:
            saveDFToMysql(dfAll, '', '餐厅家具', '餐桌', '', conn)
        elif i == 1:
            saveDFToMysql(dfAll, '', '餐厅家具', '餐边柜', '', conn)
        elif i == 2:
            saveDFToMysql(dfAll, '', '餐厅家具', '酒柜', '', conn)
        elif i == 3:
            saveDFToMysql(dfAll, '', '餐厅家具', '椅子', '', conn)
        elif i == 4:
            saveDFToMysql(dfAll, '', '餐厅家具', '特殊商品', '', conn)

    # ★书房家具(6)
    for i in range(0, 6):
        # ========顶层菜单【客厅家具】=======
        # clear network
        pyautogui.moveTo(1541, 144, 4)
        pyautogui.doubleClick()

        # filter textbox
        pyautogui.moveTo(1515, 171, 1)
        pyautogui.doubleClick()

        clip.copy('getProductBillBoardDealData')  # 先复制
        pyautogui.hotkey('ctrl', 'v')  # 再粘贴

        # old code
        sleep(waitSecond)
        pyautogui.moveTo(1015 - newfix_x, 259)
        pyautogui.click()
        # 一级
        pyautogui.moveTo(997 - newfix_x, 330 + deta * 3, 2)
        # 二级
        pyautogui.moveTo(1192 - newfix_x, 330 + deta * 3, 2)
        # 二级（变化）
        pyautogui.moveTo(1192 - newfix_x, 298 + i * deta, 2)
        pyautogui.click()

        # ===-由于需要关联，因此在这里保存信息
        # 选中第一个文件
        pyautogui.moveTo(1541, 326, 2)
        pyautogui.click()

        # 选中json区域
        pyautogui.moveTo(1830, 492, 2)
        pyautogui.click()

        sleep(2)
        pyautogui.hotkey('ctrl', 'a')

        sleep(2)
        pyautogui.hotkey('ctrl', 'c')

        # 读取剪切板内容json内容
        sleep(2)
        value = clip.paste()
        jsonFromWeb = json.loads(value)
        df = pd.read_json(json.dumps(jsonFromWeb['content']['trade']['data']))
        df.rename(
            columns={0: '排名', 1: '商品名称', 2: '成交金额指数', 3: '成交单量指数', 4: '访客指数', 5: '搜索点击指数', 6: '关注人数',
                     8: '平台商品SKUID', }, inplace=True)
        # 4个图片链接的信息
        for j in range(0, 4):
            # filter textbox
            pyautogui.moveTo(1515, 171, 1)
            pyautogui.doubleClick()

            clip.copy('getImageURL')  # 先复制
            pyautogui.hotkey('ctrl', 'v')  # 再粘贴

            # 选中第一个文件
            pyautogui.moveTo(1541, 326 + j * 20, 2)
            pyautogui.click()

            # 选中json区域
            pyautogui.moveTo(1830, 492, 2)
            pyautogui.click()

            sleep(2)
            pyautogui.hotkey('ctrl', 'a')

            sleep(2)
            pyautogui.hotkey('ctrl', 'c')

            # 读取剪切板内容json内容
            sleep(2)
            value = clip.paste()
            jsonImage = json.loads(value)
            dfImageLoop = pd.read_json(json.dumps(jsonImage['content']['data']))
            if j == 0:
                dfImage = dfImageLoop
            else:
                dfImage = pd.concat([dfImage, dfImageLoop])

        sleep(3)

        dfImage['图片链接'] = dfImage['imgSrc'].apply(fixFormatImageColumn)

        dfAll = pd.merge(df, dfImage, left_on=['平台商品SKUID'], right_on=['skuId'], how='left')
        dfAll = dfAll.drop(columns=['skuId', 'currentPrice', 'imgSrc', 7])
        dfAll = dfAll.drop_duplicates()
        # =====还需要获取图片链接信息

        # 不需要通过页面的文件下载，直接保存到数据库中了
        if i == 0:
            saveDFToMysql(dfAll, '', '书房家具', '书架', '', conn)
        elif i == 1:
            saveDFToMysql(dfAll, '', '书房家具', '电脑椅', '', conn)
        elif i == 2:
            saveDFToMysql(dfAll, '', '书房家具', '书桌', '', conn)
        elif i == 3:
            saveDFToMysql(dfAll, '', '书房家具', '电脑桌', '', conn)
        elif i == 4:
            saveDFToMysql(dfAll, '', '书房家具', '特殊商品', '', conn)
        elif i == 5:
            saveDFToMysql(dfAll, '', '书房家具', '书柜', '', conn)

    # ★储物家具(6)
    for i in range(0, 6):
        # ========顶层菜单【客厅家具】=======
        # clear network
        pyautogui.moveTo(1541, 144, 4)
        pyautogui.doubleClick()

        # filter textbox
        pyautogui.moveTo(1515, 171, 1)
        pyautogui.doubleClick()

        clip.copy('getProductBillBoardDealData')  # 先复制
        pyautogui.hotkey('ctrl', 'v')  # 再粘贴

        # old code
        sleep(waitSecond)
        pyautogui.moveTo(1015 - newfix_x, 259)
        pyautogui.click()
        # 一级
        pyautogui.moveTo(997 - newfix_x, 330 + deta * 4, 2)
        # 二级
        pyautogui.moveTo(1192 - newfix_x, 330 + deta * 4, 2)
        # 二级（变化）
        pyautogui.moveTo(1192 - newfix_x, 298 + i * deta, 2)
        pyautogui.click()

        # ===-由于需要关联，因此在这里保存信息
        # 选中第一个文件
        pyautogui.moveTo(1541, 326, 2)
        pyautogui.click()

        # 选中json区域
        pyautogui.moveTo(1830, 492, 2)
        pyautogui.click()

        sleep(2)
        pyautogui.hotkey('ctrl', 'a')

        sleep(2)
        pyautogui.hotkey('ctrl', 'c')

        # 读取剪切板内容json内容
        sleep(2)
        value = clip.paste()
        jsonFromWeb = json.loads(value)
        df = pd.read_json(json.dumps(jsonFromWeb['content']['trade']['data']))
        df.rename(
            columns={0: '排名', 1: '商品名称', 2: '成交金额指数', 3: '成交单量指数', 4: '访客指数', 5: '搜索点击指数', 6: '关注人数',
                     8: '平台商品SKUID', }, inplace=True)
        # 4个图片链接的信息
        for j in range(0, 4):
            # filter textbox
            pyautogui.moveTo(1515, 171, 1)
            pyautogui.doubleClick()

            clip.copy('getImageURL')  # 先复制
            pyautogui.hotkey('ctrl', 'v')  # 再粘贴

            # 选中第一个文件
            pyautogui.moveTo(1541, 326 + j * 20, 2)
            pyautogui.click()

            # 选中json区域
            pyautogui.moveTo(1830, 492, 2)
            pyautogui.click()

            sleep(2)
            pyautogui.hotkey('ctrl', 'a')

            sleep(2)
            pyautogui.hotkey('ctrl', 'c')

            # 读取剪切板内容json内容
            sleep(2)
            value = clip.paste()
            jsonImage = json.loads(value)
            dfImageLoop = pd.read_json(json.dumps(jsonImage['content']['data']))
            if j == 0:
                dfImage = dfImageLoop
            else:
                dfImage = pd.concat([dfImage, dfImageLoop])

        sleep(3)

        dfImage['图片链接'] = dfImage['imgSrc'].apply(fixFormatImageColumn)

        dfAll = pd.merge(df, dfImage, left_on=['平台商品SKUID'], right_on=['skuId'], how='left')
        dfAll = dfAll.drop(columns=['skuId', 'currentPrice', 'imgSrc', 7])
        dfAll = dfAll.drop_duplicates()
        # =====还需要获取图片链接信息

        # 不需要通过页面的文件下载，直接保存到数据库中了
        if i == 0:
            saveDFToMysql(dfAll, '', '储物家具', '鞋架', '', conn)
        elif i == 1:
            saveDFToMysql(dfAll, '', '储物家具', '储物/收纳用品', '', conn)
        elif i == 2:
            saveDFToMysql(dfAll, '', '储物家具', '墙面搁架', '', conn)
        elif i == 3:
            saveDFToMysql(dfAll, '', '储物家具', '层架/置物架', '', conn)
        elif i == 4:
            saveDFToMysql(dfAll, '', '储物家具', '特殊商品', '', conn)
        elif i == 5:
            saveDFToMysql(dfAll, '', '储物家具', '衣帽架', '', conn)

    # ★阳台/户外(6)
    for i in range(0, 6):
        # ========顶层菜单【客厅家具】=======
        # clear network
        pyautogui.moveTo(1541, 144, 4)
        pyautogui.doubleClick()

        # filter textbox
        pyautogui.moveTo(1515, 171, 1)
        pyautogui.doubleClick()

        clip.copy('getProductBillBoardDealData')  # 先复制
        pyautogui.hotkey('ctrl', 'v')  # 再粘贴

        # old code
        sleep(waitSecond)
        pyautogui.moveTo(1015 - newfix_x, 259)
        pyautogui.click()
        # 一级
        pyautogui.moveTo(997 - newfix_x, 330 + deta * 5, 2)
        # 二级
        pyautogui.moveTo(1192 - newfix_x, 330 + deta * 5, 2)
        # 二级（变化）
        pyautogui.moveTo(1192 - newfix_x, 298 + i * deta, 2)
        pyautogui.click()

        # ===-由于需要关联，因此在这里保存信息
        # 选中第一个文件
        pyautogui.moveTo(1541, 326, 2)
        pyautogui.click()

        # 选中json区域
        pyautogui.moveTo(1830, 492, 2)
        pyautogui.click()

        sleep(2)
        pyautogui.hotkey('ctrl', 'a')

        sleep(2)
        pyautogui.hotkey('ctrl', 'c')

        # 读取剪切板内容json内容
        sleep(2)
        value = clip.paste()
        jsonFromWeb = json.loads(value)
        df = pd.read_json(json.dumps(jsonFromWeb['content']['trade']['data']))
        df.rename(
            columns={0: '排名', 1: '商品名称', 2: '成交金额指数', 3: '成交单量指数', 4: '访客指数', 5: '搜索点击指数', 6: '关注人数',
                     8: '平台商品SKUID', }, inplace=True)
        # 4个图片链接的信息
        for j in range(0, 4):
            # filter textbox
            pyautogui.moveTo(1515, 171, 1)
            pyautogui.doubleClick()

            clip.copy('getImageURL')  # 先复制
            pyautogui.hotkey('ctrl', 'v')  # 再粘贴

            # 选中第一个文件
            pyautogui.moveTo(1541, 326 + j * 20, 2)
            pyautogui.click()

            # 选中json区域
            pyautogui.moveTo(1830, 492, 2)
            pyautogui.click()

            sleep(2)
            pyautogui.hotkey('ctrl', 'a')

            sleep(2)
            pyautogui.hotkey('ctrl', 'c')

            # 读取剪切板内容json内容
            sleep(2)
            value = clip.paste()
            jsonImage = json.loads(value)
            dfImageLoop = pd.read_json(json.dumps(jsonImage['content']['data']))
            if j == 0:
                dfImage = dfImageLoop
            else:
                dfImage = pd.concat([dfImage, dfImageLoop])

        sleep(3)

        dfImage['图片链接'] = dfImage['imgSrc'].apply(fixFormatImageColumn)

        dfAll = pd.merge(df, dfImage, left_on=['平台商品SKUID'], right_on=['skuId'], how='left')
        dfAll = dfAll.drop(columns=['skuId', 'currentPrice', 'imgSrc', 7])
        dfAll = dfAll.drop_duplicates()
        # =====还需要获取图片链接信息

        # 不需要通过页面的文件下载，直接保存到数据库中了
        if i == 0:
            saveDFToMysql(dfAll, '', '阳台/户外', '晾衣架', '', conn)
        elif i == 1:
            saveDFToMysql(dfAll, '', '阳台/户外', '花架/装饰架', '', conn)
        elif i == 2:
            saveDFToMysql(dfAll, '', '阳台/户外', '家用梯', '', conn)
        elif i == 3:
            saveDFToMysql(dfAll, '', '阳台/户外', '户外家具', '', conn)
        elif i == 4:
            saveDFToMysql(dfAll, '', '阳台/户外', '折叠床', '', conn)
        elif i == 5:
            saveDFToMysql(dfAll, '', '阳台/户外', '特殊商品', '', conn)

    # ★商业办公(9->10)
    for i in range(0, 10):
        # ========顶层菜单【客厅家具】=======
        # clear network
        pyautogui.moveTo(1541, 144, 4)
        pyautogui.doubleClick()

        # filter textbox
        pyautogui.moveTo(1515, 171, 1)
        pyautogui.doubleClick()

        clip.copy('getProductBillBoardDealData')  # 先复制
        pyautogui.hotkey('ctrl', 'v')  # 再粘贴

        # old code
        sleep(waitSecond)
        pyautogui.moveTo(1015 - newfix_x, 259)
        pyautogui.click()
        # 一级
        pyautogui.moveTo(997 - newfix_x, 330 + deta * 6, 2)
        # 二级
        pyautogui.moveTo(1192 - newfix_x, 330 + deta * 6, 2)
        # 二级（变化）
        if i <= 8:
            pyautogui.moveTo(1192 - newfix_x, 298 + i * deta, 2)
        elif i == 9:
            pyautogui.scroll(-20)
            pyautogui.moveTo(1192 - newfix_x, 298 + 8 * deta + deta - 15, 2)

        pyautogui.click()

        # ===-由于需要关联，因此在这里保存信息
        # 选中第一个文件
        pyautogui.moveTo(1541, 326, 2)
        pyautogui.click()

        # 选中json区域
        pyautogui.moveTo(1830, 492, 2)
        pyautogui.click()

        sleep(2)
        pyautogui.hotkey('ctrl', 'a')

        sleep(2)
        pyautogui.hotkey('ctrl', 'c')

        # 读取剪切板内容json内容
        sleep(2)
        value = clip.paste()
        jsonFromWeb = json.loads(value)
        df = pd.read_json(json.dumps(jsonFromWeb['content']['trade']['data']))
        df.rename(
            columns={0: '排名', 1: '商品名称', 2: '成交金额指数', 3: '成交单量指数', 4: '访客指数', 5: '搜索点击指数', 6: '关注人数',
                     8: '平台商品SKUID', }, inplace=True)
        # 4个图片链接的信息
        for j in range(0, 4):
            # filter textbox
            pyautogui.moveTo(1515, 171, 1)
            pyautogui.doubleClick()

            clip.copy('getImageURL')  # 先复制
            pyautogui.hotkey('ctrl', 'v')  # 再粘贴

            # 选中第一个文件
            pyautogui.moveTo(1541, 326 + j * 20, 2)
            pyautogui.click()

            # 选中json区域
            pyautogui.moveTo(1830, 492, 2)
            pyautogui.click()

            sleep(2)
            pyautogui.hotkey('ctrl', 'a')

            sleep(2)
            pyautogui.hotkey('ctrl', 'c')

            # 读取剪切板内容json内容
            sleep(2)
            value = clip.paste()
            jsonImage = json.loads(value)
            dfImageLoop = pd.read_json(json.dumps(jsonImage['content']['data']))
            if j == 0:
                dfImage = dfImageLoop
            else:
                dfImage = pd.concat([dfImage, dfImageLoop])

        sleep(3)

        dfImage['图片链接'] = dfImage['imgSrc'].apply(fixFormatImageColumn)

        dfAll = pd.merge(df, dfImage, left_on=['平台商品SKUID'], right_on=['skuId'], how='left')
        dfAll = dfAll.drop(columns=['skuId', 'currentPrice', 'imgSrc', 7])
        dfAll = dfAll.drop_duplicates()
        # =====还需要获取图片链接信息

        # 不需要通过页面的文件下载，直接保存到数据库中了
        if i == 0:
            saveDFToMysql(dfAll, '', '商业办公', '屏风工位', '', conn)
        elif i == 1:
            saveDFToMysql(dfAll, '', '商业办公', '班台/班桌', '', conn)
        elif i == 2:
            saveDFToMysql(dfAll, '', '商业办公', '桑拿/足浴/健身家具', '', conn)
        elif i == 3:
            saveDFToMysql(dfAll, '', '商业办公', '会议台/桌', '', conn)
        elif i == 4:
            saveDFToMysql(dfAll, '', '商业办公', '办公沙发', '', conn)
        elif i == 5:
            saveDFToMysql(dfAll, '', '商业办公', '文件柜/办公柜', '', conn)
        elif i == 6:
            saveDFToMysql(dfAll, '', '商业办公', '办公前台/收银台', '', conn)
        elif i == 7:
            saveDFToMysql(dfAll, '', '商业办公', '货架/展示架', '', conn)
        elif i == 8:
            saveDFToMysql(dfAll, '', '商业办公', '麻将机', '', conn)
        elif i == 9:
            saveDFToMysql(dfAll, '', '商业办公', '特殊商品', '', conn)

    # ★套房家具(5)
    for i in range(0, 5):
        # ========顶层菜单【客厅家具】=======
        # clear network
        pyautogui.moveTo(1541, 144, 4)
        pyautogui.doubleClick()

        # filter textbox
        pyautogui.moveTo(1515, 171, 1)
        pyautogui.doubleClick()

        clip.copy('getProductBillBoardDealData')  # 先复制
        pyautogui.hotkey('ctrl', 'v')  # 再粘贴

        # old code
        sleep(waitSecond)
        pyautogui.moveTo(1015 - newfix_x, 259)
        pyautogui.click()
        # 一级
        pyautogui.moveTo(997 - newfix_x, 330 + deta * 7, 2)
        # 二级
        pyautogui.moveTo(1192 - newfix_x, 330 + deta * 7, 2)
        # 二级（变化）
        pyautogui.moveTo(1192 - newfix_x, 298 + i * deta, 2)
        pyautogui.click()

        # ===-由于需要关联，因此在这里保存信息
        # 选中第一个文件
        pyautogui.moveTo(1541, 326, 2)
        pyautogui.click()

        # 选中json区域
        pyautogui.moveTo(1830, 492, 2)
        pyautogui.click()

        sleep(2)
        pyautogui.hotkey('ctrl', 'a')

        sleep(2)
        pyautogui.hotkey('ctrl', 'c')

        # 读取剪切板内容json内容
        sleep(2)
        value = clip.paste()
        jsonFromWeb = json.loads(value)
        df = pd.read_json(json.dumps(jsonFromWeb['content']['trade']['data']))
        df.rename(
            columns={0: '排名', 1: '商品名称', 2: '成交金额指数', 3: '成交单量指数', 4: '访客指数', 5: '搜索点击指数', 6: '关注人数',
                     8: '平台商品SKUID', }, inplace=True)
        # 4个图片链接的信息
        for j in range(0, 4):
            # filter textbox
            pyautogui.moveTo(1515, 171, 1)
            pyautogui.doubleClick()

            clip.copy('getImageURL')  # 先复制
            pyautogui.hotkey('ctrl', 'v')  # 再粘贴

            # 选中第一个文件
            pyautogui.moveTo(1541, 326 + j * 20, 2)
            pyautogui.click()

            # 选中json区域
            pyautogui.moveTo(1830, 492, 2)
            pyautogui.click()

            sleep(2)
            pyautogui.hotkey('ctrl', 'a')

            sleep(2)
            pyautogui.hotkey('ctrl', 'c')

            # 读取剪切板内容json内容
            sleep(2)
            value = clip.paste()
            jsonImage = json.loads(value)
            dfImageLoop = pd.read_json(json.dumps(jsonImage['content']['data']))
            if j == 0:
                dfImage = dfImageLoop
            else:
                dfImage = pd.concat([dfImage, dfImageLoop])

        sleep(3)

        dfImage['图片链接'] = dfImage['imgSrc'].apply(fixFormatImageColumn)

        dfAll = pd.merge(df, dfImage, left_on=['平台商品SKUID'], right_on=['skuId'], how='left')
        dfAll = dfAll.drop(columns=['skuId', 'currentPrice', 'imgSrc', 7])
        dfAll = dfAll.drop_duplicates()
        # =====还需要获取图片链接信息

        # 不需要通过页面的文件下载，直接保存到数据库中了
        if i == 0:
            saveDFToMysql(dfAll, '', '套房家具', '卧室套房', '', conn)
        elif i == 1:
            saveDFToMysql(dfAll, '', '套房家具', '客厅套房', '', conn)
        elif i == 2:
            saveDFToMysql(dfAll, '', '套房家具', '餐厅套房', '', conn)
        elif i == 3:
            saveDFToMysql(dfAll, '', '套房家具', '儿童套房', '', conn)
        elif i == 4:
            saveDFToMysql(dfAll, '', '套房家具', '书房套房', '', conn)

def saveDFToMysql(df, cat1, cat2, cat3, catLeaf, conn):
    # sleep(5)
    df['模式'] = 'POP'
    df['第几周'] = int(time.strftime("%W")) + 1
    df['导入月份'] = month
    df['导入时间'] = datetime.datetime.now()
    df['cat1'] = cat1
    df['cat2'] = cat2
    df['cat3'] = cat3
    df['catLeaf'] = catLeaf
    df['运行模式'] = runMode
    df['统计月'] = ''
    df['统计周'] = ''
    df['统计日'] = ''
    df['统计日周月'] = ''
    if runMode == '月模式':
        df['统计月'] = str(datetime.datetime.now().month - 1) + "月"
        df['统计日周月'] = df['统计月']
    if runMode == '周模式':
        df['统计周'] = getLastWeekOfYear(datetime.datetime.now().year, datetime.datetime.now().month,datetime.datetime.now().day)
        df['统计日周月'] = df['统计周']
    if runMode == '日模式':
        df['统计日'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
            "%Y-%m-%d %H:%M:%S")  # 减一天，统计的是昨天的数据
        df['统计日周月'] = df['统计日']
    # 解决可能出现的超时问题bugfix 2020.08.18
    conn.connection.connection.ping(reconnect=True)
    # df.reset_index(drop=True, inplace=True)
    # 京东_POP_行业大盘_商品榜单_交易榜单TOP100
    # 京东_POP_行业大盘_商品榜单_交易榜单TOP100_debug
    df.to_sql(name='京东_POP_行业大盘_商品榜单_交易榜单TOP100', con=conn, if_exists='append', index=False)

def deleteWholeWrongData(engine):
    # 原则上都是某个模式下的某月，某天的数据全部删除，再重新跑
    strDayWeekMonth = ''
    if runMode == '月模式':
        strDayWeekMonth = str(datetime.datetime.now().month - 1) + "月"
    if runMode == '周模式':
        strDayWeekMonth = getLastWeekOfYear(datetime.datetime.now().year, datetime.datetime.now().month,
                                            datetime.datetime.now().day)
    if runMode == '日模式':
        strDayWeekMonth = strDayWeekMonth(datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
            "%Y-%m-%d %H:%M:%S")  # 减一天，统计的是昨天的数据
    sql = "delete from " + modeName + " where 运行模式='" + runMode + "' and 统计日周月='" + strDayWeekMonth + "'"
    engine.execute(sql)

def executeCatchWeb(engine, conn):
    try:
        LogTaskAndMachine('京东_POP_行业大盘_商品榜单_交易榜单TOP100', engine, conn, '', runMode)
        deleteWholeWrongData(engine)
        # 顶部菜单
        TopAndLeftMenu()

        # 循环下面的菜单
        LoopMenu(conn)
    except Exception as e:
        traceback.print_exc()
        sendAlert('【异常中断】' + modeName, '异常:' + str(e) + '|报错文件:' + os.path.split(__file__)[-1], engine, conn)
        print('【异常中断】' + modeName, '异常:' + str(e) + '|报错文件:' + os.path.split(__file__)[-1])
        return
    sendFinalSuccessEmail('●正常完成●' + modeName, '', engine, conn, modeName, runMode)


if __name__ == '__main__':
    # 连接database
    engine, conn = getConn()
    executeCatchWeb(engine, conn)
